package org.sean.contactdemo.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.sean.contactdemo.domain.GenderType;
import org.sean.contactdemo.domain.User;
import org.sean.contactdemo.util.DateHelper;
import org.sean.contactdemo.util.StringHelper;

public class UserDAO extends AbstractDAO {

	public UserDAO(){
		super();
	}
	
	public UserDAO(Connection connection){
		super(connection);
	}
	
	public User findUserByID(int userId){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from cd_user where user_id =").append(userId);
		User user = null;
		ResultSet rs = getResultSetAfterQuery(sql.toString());
		try {
			rs.last();
			if (rs != null && rs.getRow() == 1){
				user = new User();
				user.setUserId(rs.getInt("user_id"));
				user.setUserAccount(rs.getString("user_account"));
				user.setUserPassword(rs.getString("user_password"));
				user.setUserName(rs.getString("user_name"));
				user.setUserGender(rs.getString("user_gender").equals("0") ? GenderType.MALE : GenderType.FEMALE);
				user.setUserBirth(rs.getDate("user_birth"));
				user.setMail(rs.getString("mail"));
				user.setZipcode(rs.getString("zipcode"));
				user.setAddress(rs.getString("address"));
				user.setAreacode(rs.getString("areacode"));
				user.setPhone(rs.getString("phone"));
				user.setMobile(rs.getString("mobile"));
				user.setLastLogin(rs.getDate("last_login"));
			}
		} catch (SQLException e) {
			error(e);
		} finally {
			releaseDBResource(rs);
		}
		return user;
	}
	
	public User findUserByAccount(String userAccount){
		StringBuffer sql = new StringBuffer();
		sql.append("select user_id from cd_user where user_account = '").append(userAccount).append("'");
		User user = null;
		ResultSet rs = getResultSetAfterQuery(sql.toString());
		try {
			rs.last();
			if (rs != null && rs.getRow() == 1){
				int userId = rs.getInt("user_id");
				user = findUserByID(userId);
			}
		} catch (SQLException e){
			error(e);
		} finally {
			releaseDBResource(rs);
		}
		return user;
	}
	
	public User addUser(User newUser){
		StringBuffer sql = new StringBuffer();
		sql.append("insert into cd_user(user_account, user_password, user_name, user_gender, user_birth, mail, zipcode, address, areacode, phone, mobile, last_login) values (");
		sql.append("'" + newUser.getUserAccount() + "'").append(",");
		sql.append("'" + newUser.getUserPassword() + "'").append(",");
		if (StringHelper.isNotEmpty(newUser.getUserName())){
			sql.append("'" + newUser.getUserName() + "'").append(",");
		} else {
			sql.append("'',");
		}
		sql.append("'").append(newUser.getUserGender().equals(GenderType.MALE)? 0 : 1).append("',");
		if (newUser.getUserBirth() != null){
			sql.append("'" + DateHelper.getFormattedDate(newUser.getUserBirth(), DateHelper.PARAM_FULL_DATETIMEFORMAT) + "'").append(",");
		} else {
			sql.append("'',");
		}
		if (StringHelper.isNotEmpty(newUser.getMail())){
			sql.append("'" + newUser.getMail() + "'").append(",");
		} else {
			sql.append("'',");
		}
		if (StringHelper.isNotEmpty(newUser.getZipcode())){
			sql.append("'" + newUser.getZipcode() + "'").append(",");
		} else {
			sql.append("'',");
		}
		if (StringHelper.isNotEmpty(newUser.getAddress())){
			sql.append("'" + newUser.getAddress() + "'").append(",");
		} else {
			sql.append("'',");
		}
		if (StringHelper.isNotEmpty(newUser.getAreacode())){
			sql.append("'" + newUser.getAreacode() + "'").append(",");
		} else {
			sql.append("'',");
		}
		if (StringHelper.isNotEmpty(newUser.getPhone())){
			sql.append("'" + newUser.getPhone() + "'").append(",");
		} else {
			sql.append("'',");
		}
		if (StringHelper.isNotEmpty(newUser.getMobile())){
			sql.append("'" + newUser.getMobile() + "'").append(",");
		} else {
			sql.append("'',");
		}
		if (newUser.getLastLogin() != null){
			sql.append("'" + DateHelper.getFormattedDate(newUser.getLastLogin(), DateHelper.PARAM_FULL_DATETIMEFORMAT) + "'");
		} else {
			sql.append("''");
		}
		sql.append(")");
		boolean bSuccess = excute(sql.toString());
		User user = null;
		if (bSuccess){
			user = findUserByAccount(newUser.getUserAccount());
		}
		return user;
	}
	public List<User> getUserResult(String sql){
		List<User> users = new ArrayList<User>();
		ResultSet rs = getResultSetAfterQuery(sql);
		try {
			if (rs != null){
				while (rs.next()){
					int groupId = rs.getInt("user_id");
					User user = findUserByID(groupId);
					if (user != null){
						users.add(user);
					}
				}
			}
		} catch (SQLException e){
			error(e);
		} finally {
			releaseDBResource(rs);
		}
		return users;
	}
	
	public User loginWithUser(String userAccount, String userPassword){
		User user = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select user_id from cd_user where ");
		sql.append("user_account = '").append(userAccount).append("'");
		sql.append(" and ");
		sql.append("user_password = '").append(userPassword).append("'");
		List<User> users = getUserResult(sql.toString());
		if (users.size() == 1){
			user = users.get(0);
		}
		return user;
	}
	
	public void updateUser(User userNew){
		StringBuffer sql = new StringBuffer();
		//this.excute(sql.toString());
	}
}
